﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;

namespace DALL.SysManage
{
   public class T_PUB_MENU
    {
       public T_PUB_MENU()
       { }


       /// <summary>
       /// 智能提示菜单名称
       /// </summary>
       /// <returns></returns>
       public DataSet GetMenuName()
       {
           StringBuilder strSql = new StringBuilder();
           strSql.Append("select DISTINCT MENU_NAME FROM T_PUB_MENU ");
           Database db = DatabaseFactory.CreateDatabase();
           DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
           return db.ExecuteDataSet(CommandType.Text, strSql.ToString());
       }

       #region  Method

       /// <summary>
       /// 得到用户菜单
       /// </summary>
       public List<Model.SysManage.T_PUB_MENU> GetUserMenuList(string strWhere)
       {
           StringBuilder varname1 = new StringBuilder();
           varname1.Append("SELECT DISTINCT m.MENU_ID, \n");
           varname1.Append("                m.MENU_NAME, \n");
           varname1.Append("                m.URL, \n");
           varname1.Append("                m.PARENT_ID, \n");
           varname1.Append("                m.SEQUENCE \n");
           varname1.Append("FROM   T_PUB_USERROLE s, \n");
           varname1.Append("       T_PUB_ROLEMENU u, \n");
           varname1.Append("       T_PUB_MENU m \n");
           varname1.Append("WHERE  s.ROLE_ID = u.ROLE_ID \n");
           varname1.Append("       AND u.MENU_ID = m.MENU_ID \n");

           if (!string.IsNullOrEmpty(strWhere))
           {
               varname1.Append(strWhere);
           }
           varname1.Append("ORDER  BY PARENT_ID, \n");
           varname1.Append("          SEQUENCE ");
           List<Model.SysManage.T_PUB_MENU> list = new List<Model.SysManage.T_PUB_MENU>();
           Database db = DatabaseFactory.CreateDatabase();
           using (IDataReader dataReader = db.ExecuteReader(CommandType.Text, varname1.ToString()))
           {
               while (dataReader.Read())
               {
                   list.Add(ReaderBind(dataReader));
               }
           }
           return list;
       }

       /// <summary>
       /// 增加一条数据
       /// </summary>
       public int Add(Model.SysManage.T_PUB_MENU model)
       {
           StringBuilder strSql = new StringBuilder();
           strSql.Append("insert into T_PUB_MENU(");
           strSql.Append("MENU_NAME,URL,PARENT_ID,SEQUENCE)");

           strSql.Append(" values (");
           strSql.Append("@MENU_NAME,@URL,@PARENT_ID,@SEQUENCE)");
           strSql.Append(";select @@IDENTITY");
           Database db = DatabaseFactory.CreateDatabase();
           DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
           db.AddInParameter(dbCommand, "MENU_NAME", DbType.String, model.MENU_NAME);
           db.AddInParameter(dbCommand, "URL", DbType.String, model.URL);
           db.AddInParameter(dbCommand, "PARENT_ID", DbType.Int32, model.PARENT_ID);
           db.AddInParameter(dbCommand, "SEQUENCE", DbType.Int32, model.SEQUENCE);
           int result;
           object obj = db.ExecuteScalar(dbCommand);
           if (!int.TryParse(obj.ToString(), out result))
           {
               return 0;
           }
           return result;
       }
       /// <summary>
       /// 更新一条数据
       /// </summary>
       public bool Update(Model.SysManage.T_PUB_MENU model)
       {
           StringBuilder strSql = new StringBuilder();
           strSql.Append("update T_PUB_MENU set ");
           strSql.Append("MENU_NAME=@MENU_NAME,");
           strSql.Append("URL=@URL,");
           strSql.Append("PARENT_ID=@PARENT_ID,");
           strSql.Append("SEQUENCE=@SEQUENCE");
           strSql.Append(" where MENU_ID=@MENU_ID ");
           Database db = DatabaseFactory.CreateDatabase();
           DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
           db.AddInParameter(dbCommand, "MENU_ID", DbType.Int32, model.MENU_ID);
           db.AddInParameter(dbCommand, "MENU_NAME", DbType.String, model.MENU_NAME);
           db.AddInParameter(dbCommand, "URL", DbType.String, model.URL);
           db.AddInParameter(dbCommand, "PARENT_ID", DbType.Int32, model.PARENT_ID);
           db.AddInParameter(dbCommand, "SEQUENCE", DbType.Int32, model.SEQUENCE);
           int rows = db.ExecuteNonQuery(dbCommand);

           if (rows > 0)
           {
               return true;
           }
           else
           {
               return false;
           }
       }

       /// <summary>
       /// 删除一条数据
       /// </summary>
       public bool Delete(int MENU_ID)
       {

           StringBuilder strSql = new StringBuilder();
           strSql.Append("delete from T_PUB_MENU ");
           strSql.Append(" where MENU_ID=@MENU_ID ");
           Database db = DatabaseFactory.CreateDatabase();
           DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
           db.AddInParameter(dbCommand, "MENU_ID", DbType.Int32, MENU_ID);
           int rows = db.ExecuteNonQuery(dbCommand);

           if (rows > 0)
           {
               return true;
           }
           else
           {
               return false;
           }
       }

       /// <summary>
       /// 得到一个对象实体
       /// </summary>
       public Model.SysManage.T_PUB_MENU GetModel(int MENU_ID)
       {

           StringBuilder strSql = new StringBuilder();
           strSql.Append("select MENU_ID,MENU_NAME,URL,PARENT_ID,SEQUENCE from T_PUB_MENU ");
           strSql.Append(" where MENU_ID=@MENU_ID ");
           Database db = DatabaseFactory.CreateDatabase();
           DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
           db.AddInParameter(dbCommand, "MENU_ID", DbType.Int32, MENU_ID);
           Model.SysManage.T_PUB_MENU model = null;
           using (IDataReader dataReader = db.ExecuteReader(dbCommand))
           {
               if (dataReader.Read())
               {
                   model = ReaderBind(dataReader);
               }
           }
           return model;
       }

       /// <summary>
       /// 获得数据列表
       /// </summary>
       public DataSet GetList(string strWhere)
       {
           StringBuilder strSql = new StringBuilder();
           strSql.Append("select MENU_ID,MENU_NAME,URL,PARENT_ID,SEQUENCE ");
           strSql.Append(" FROM T_PUB_MENU ");
           if (strWhere.Trim() != "")
           {
               strSql.Append(" where " + strWhere);
           }
           Database db = DatabaseFactory.CreateDatabase();
           return db.ExecuteDataSet(CommandType.Text, strSql.ToString());
       }



       /// <summary>
       /// 获得数据列表（比DataSet效率高，推荐使用）
       /// </summary>
       public List<Model.SysManage.T_PUB_MENU> GetListArray(string strWhere)
       {
           StringBuilder strSql = new StringBuilder();
           strSql.Append("select MENU_ID,MENU_NAME,URL,PARENT_ID,SEQUENCE ");
           strSql.Append(" FROM T_PUB_MENU ");
           if (strWhere.Trim() != "")
           {
               strSql.Append(" where " + strWhere);
           }
           List<Model.SysManage.T_PUB_MENU> list = new List<Model.SysManage.T_PUB_MENU>();
           Database db = DatabaseFactory.CreateDatabase();
           using (IDataReader dataReader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
           {
               while (dataReader.Read())
               {
                   list.Add(ReaderBind(dataReader));
               }
           }
           return list;
       }


       /// <summary>
       /// 对象实体绑定数据
       /// </summary>
       public Model.SysManage.T_PUB_MENU ReaderBind(IDataReader dataReader)
       {
           Model.SysManage.T_PUB_MENU model = new Model.SysManage.T_PUB_MENU();
           object ojb;
           ojb = dataReader["MENU_ID"];
           if (ojb != null && ojb != DBNull.Value)
           {
               model.MENU_ID = (int)ojb;
           }
           model.MENU_NAME = dataReader["MENU_NAME"].ToString();
           model.URL = dataReader["URL"].ToString();
           ojb = dataReader["PARENT_ID"];
           if (ojb != null && ojb != DBNull.Value)
           {
               model.PARENT_ID = (int)ojb;
           }
           ojb = dataReader["SEQUENCE"];
           if (ojb != null && ojb != DBNull.Value)
           {
               model.SEQUENCE = (int)ojb;
           }
           return model;
       }

       #endregion  Method
    }
}
